import requests
from bs4 import BeautifulSoup
import csv
import openpyxl

# 设置请求头，防止被网站拦截  116
headers = {
    "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) "
                  "AppleWebKit/537.36 (KHTML, like Gecko) "
                  "Chrome/122.0.0.0 Safari/537.36"
}

url = "https://gaokao.eol.cn/e_html/gk/2022/211yx/index.html"
resp = requests.get(url, headers=headers, timeout=10)
resp.encoding = resp.apparent_encoding
html = resp.text

soup = BeautifulSoup(html, "html.parser")
table = soup.find("table")

data = []
current_region = None
remaining_rows = 0  # 用于跟踪rowspan剩余行数

trs = table.find_all("tr")
for tr in trs:
    tds = tr.find_all("td")
    if not tds:
        continue

    # 跳过表头
    if "地区" in tds[0].get_text():
        continue

    if len(tds) == 3:
        # 三个单元格的情况：地区、学校、学科
        region_cell = tds[0]
        school = tds[1].get_text(strip=True)
        subjects = tds[2].get_text(strip=True)
        
        # 检查是否有rowspan属性
        if region_cell.has_attr('rowspan'):
            try:
                remaining_rows = int(region_cell.get('rowspan')) - 1
            except ValueError:
                remaining_rows = 0
        else:
            remaining_rows = 0
            
        current_region = region_cell.get_text(strip=True)
        data.append([current_region, school, subjects])
        
    elif len(tds) == 2 and (current_region or remaining_rows > 0):
        # 两个单元格的情况：学校、学科（使用之前记录的地区）
        school = tds[0].get_text(strip=True)
        subjects = tds[1].get_text(strip=True)
        data.append([current_region, school, subjects])
        if remaining_rows > 0:
            remaining_rows -= 1
            
    elif len(tds) == 1 and (current_region or remaining_rows > 0):
        # 一个单元格的情况：只有学校（使用之前记录的地区）
        school = tds[0].get_text(strip=True)
        data.append([current_region, school, ""])
        if remaining_rows > 0:
            remaining_rows -= 1

# -------- 保存 CSV --------
csv_file = "211_universities.csv"
with open(csv_file, "w", newline="", encoding="utf-8-sig") as f:
    writer = csv.writer(f)
    writer.writerow(["地区", "学校名称", "一流学科建设名单"])
    writer.writerows(data)

# -------- 保存 Excel --------
excel_file = "211_universities.xlsx"
wb = openpyxl.Workbook()
ws = wb.active
ws.title = "211高校一流学科"

ws.append(["地区", "学校名称", "一流学科建设名单"])
for row in data:
    ws.append(row)

wb.save(excel_file)

print(f"✅ 数据已保存为 {csv_file} 和 {excel_file}")